﻿using Newtonsoft.Json;
using ProjBackEnd.Common;
using ProjBackEnd.Common.Helper;
using ProjBackEnd.Models;
using ProjBackEnd.Models.AppModel.AppApproveModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ProjBackEnd.Service
{
    public sealed partial class AppApproveService
    {
        /// <summary>
        /// 获取企业及待结算审批数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public string FindCompanyWaitFNApprove(GetCompanyWaitYWApproveModel model)
        {
            string conn = Function.GetConnectionString();

            GetCompanyWaitYWApproveResultModel resultObj = new GetCompanyWaitYWApproveResultModel();

            try
            {
                if (model == null)
                {
                    throw new Exception("对象为空！");
                }

                if (string.IsNullOrEmpty(model.memberid))
                {
                    throw new Exception("用户标识不能为空！");
                }

                string memberid = model.memberid.Trim();

                string sql = $@"select FIN_FEENOTICE.comcd,
	                                        CD_COMTB.comnm,
	                                        count(*) as nums
                                        from FIN_FEENOTICE
	                                        inner join FIN_REVFLOW ON FIN_FEENOTICE.fnid = FIN_REVFLOW.applyid
	                                        inner join CD_COMTB on CD_COMTB.comcd = FIN_FEENOTICE.comcd
                                        where (FIN_REVFLOW.revlb1 = 'N' and FIN_REVFLOW.reviewer1 = '{memberid}' 
                                                    OR FIN_REVFLOW.revlb2 = 'N' and FIN_REVFLOW.reviewer2 = '{memberid}') 
                                            and FIN_REVFLOW.applytp = 'FN'
                                        group by FIN_FEENOTICE.comcd,
	                                        CD_COMTB.comnm
                                        having count(*) > 0";

                //获取数据
                DataSet dsInfo = SqlHelper.GetDataSet(sql, conn);

                List<GetCompanyWaitYWApproveResultDataModel> lst = new List<GetCompanyWaitYWApproveResultDataModel>();

                foreach(DataRow row in dsInfo.Tables[0].Rows)
                {
                    GetCompanyWaitYWApproveResultDataModel data = new GetCompanyWaitYWApproveResultDataModel();

                    data.companycd = row["comcd"].ToString().Trim();
                    data.companyname = row["comnm"].ToString().Trim();
                    data.nums = row["nums"] == DBNull.Value ? 0 : Convert.ToInt32(row["nums"]);//.ToString().Trim();

                    lst.Add(data);
                }

                resultObj.code = "0";
                resultObj.message = "";
                resultObj.data = lst;
                return JsonConvert.SerializeObject(resultObj);
            }
            catch (Exception ex)
            {
                resultObj.code = "99";
                resultObj.message = ex.Message;
                resultObj.data = null;
                return JsonConvert.SerializeObject(resultObj);
            }
        }

        /// <summary>
        /// 取待审批的结算单
        /// </summary> 
        public string FindWaitFNApproveBill(FindWaitFNApproveBillModel model)
        {
            string conn = Function.GetConnectionString();

            FindWaitFNApproveBillResultModel resultObj = new FindWaitFNApproveBillResultModel();

            try
            {
                if (model == null)
                {
                    throw new Exception("对象为空！");
                }

                if (string.IsNullOrEmpty(model.memberid))
                {
                    throw new Exception("用户标识不能为空！");
                }

                if (string.IsNullOrEmpty(model.companycd))
                {
                    throw new Exception("公司代码不能为空！");
                }

                string memberid = model.memberid.Trim();
                string companycd = model.companycd.Trim();

                string sql = $@"select 
	                                FIN_FEENOTICE.fnid,
                                    FIN_BILLTB.billid,
	                                FIN_FEENOTICE.cusid,
	                                ISNULL(CRM_CUSTOMERTB.cusnm, '') AS cusname,	--客户
	                                (CASE WHEN FIN_FEENOTICE.rplb = 'R' THEN '收款' ELSE '付款' END) AS rplbnm, --收付类型
	                                FIN_FEENOTICE.amt_settle,		--审批金额
	                                ISNULL(FIN_BILLTB.amt_pre, 0) AS amt_pre,			--预结金额,
	                                case when FIN_FEENOTICE.rplb = 'R' then FIN_BILLTB.amt_rec else FIN_BILLTB.amt_pay end as amt_total,	--账单总额
	                                case when FIN_FEENOTICE.rplb = 'R' then FIN_BILLTB.amt_rec_ed else FIN_BILLTB.amt_pay_ed end as amt_settled_bill,	--已结金额
	                                FIN_FEENOTICE.remark,
	                                ISNULL(CD_GSKM.speccn, '') AS billtpnm, --科目
	                                FIN_FEENOTICE.createid,
	                                ISNULL(SYS_USERTB.nickname, '') AS createname,
	                                FIN_REVFLOW.submitdt
                            from FIN_FEENOTICE
	                            inner join FIN_REVFLOW ON FIN_FEENOTICE.fnid = FIN_REVFLOW.applyid
	                            inner join CD_COMTB on CD_COMTB.comcd = FIN_FEENOTICE.comcd
								LEFT OUTER JOIN  FIN_BILLTB ON FIN_FEENOTICE.billid = FIN_BILLTB.billid
								LEFT OUTER JOIN CRM_CUSTOMERTB ON CRM_CUSTOMERTB.cusid = FIN_FEENOTICE.cusid
								LEFT OUTER JOIN CD_BASIC AS CD_GSKM ON CD_GSKM.cd =.FIN_BILLTB.billtp AND  CD_GSKM.attrcd = 'GSKM'
								LEFT OUTER JOIN SYS_USERTB ON FIN_FEENOTICE.createid = SYS_USERTB.memberid
                            where (FIN_REVFLOW.revlb1 = 'N' and FIN_REVFLOW.reviewer1 = '{memberid}' 
                                        OR FIN_REVFLOW.revlb2 = 'N' and FIN_REVFLOW.reviewer2 = '{memberid}') 
                                and FIN_REVFLOW.applytp = 'FN'
								and FIN_FEENOTICE.comcd = '{companycd}'";


                //获取数据
                DataSet dsInfo = SqlHelper.GetDataSet(sql, conn);

                List<FindWaitFNApproveBillResultDataModel> lst = new List<FindWaitFNApproveBillResultDataModel>();

                foreach (DataRow row in dsInfo.Tables[0].Rows)
                {
                    FindWaitFNApproveBillResultDataModel data = new FindWaitFNApproveBillResultDataModel();

                    data.fnid = row["fnid"].ToString().Trim();
                    data.billid = row["billid"].ToString().Trim();
                    data.cusname = row["cusname"].ToString().Trim();
                    data.cusid = row["cusid"].ToString().Trim();
                    data.rplbnm = row["rplbnm"].ToString().Trim();
                    data.amt_settle = row["amt_settle"].ToString().Trim();
                    data.amt_pre = row["amt_pre"].ToString().Trim();
                    data.amt_total = row["amt_total"].ToString().Trim();
                    data.amt_settled_bill = row["amt_settled_bill"].ToString().Trim();
                    data.billtpnm = row["billtpnm"].ToString().Trim();
                    data.remark = row["remark"].ToString().Trim();
                    data.createid = row["createid"].ToString().Trim();
                    data.createname = row["createname"].ToString().Trim();
                    data.submitdt = row["submitdt"] == DBNull.Value ? "" : Convert.ToDateTime(row["submitdt"]).ToString("yyyy-MM-dd HH:mm");


                    lst.Add(data);
                }

                resultObj.code = "0";
                resultObj.message = "";
                resultObj.data = lst;
                return JsonConvert.SerializeObject(resultObj);
            }
            catch (Exception ex)
            {
                resultObj.code = "99";
                resultObj.message = ex.Message;
                resultObj.data = null;
                return JsonConvert.SerializeObject(resultObj);
            }
        }


        /// <summary>
        /// 取结算单明细
        /// </summary> 
        public string FindWaitFNApproveBillDetail(FindWaitFNApproveBillDetailModel model)
        {
            string conn = Function.GetConnectionString();

            FindWaitFNApproveBillDetailResultModel resultObj = new FindWaitFNApproveBillDetailResultModel();

            try
            {
                if (model == null)
                {
                    throw new Exception("对象为空！");
                }

                if (string.IsNullOrEmpty(model.billid))
                {
                    throw new Exception("billid不能为空！");
                }

                 


                string billid = model.billid.Trim();

                string sql = $@"select VIEW_BIZ_FEEDETAILINFO.billid,
	                                    VIEW_BIZ_FEEDETAILINFO.objtp,   --账单名称 
	                                    case when VIEW_BIZ_FEEDETAILINFO.rplb = 'R' then VIEW_BIZ_FEEDETAILINFO.amt_rectotal else BIZ_PROJDETAIL.amt_seltotal end as amt_rectotaldtl,   --销售总价
	                                    VIEW_BIZ_FEEDETAILINFO.amt_paytotal,	--本次应付
	                                    BIZ_PROJDETAIL.dtlnum,				--销售数量
	                                    VIEW_BIZ_FEEDETAILINFO.num,			--本次数量 
	                                    BIZ_PROJDETAIL.amt_selprice,		--销售单价
	                                    VIEW_BIZ_FEEDETAILINFO.amt_payprice,	--本次单价
	                                    VIEW_BIZ_FEEDETAILINFO.amt_rectotal,		--已审批总额	
	                                    VIEW_BIZ_FEEDETAILINFO.model as xh			--型号	 
                                    from VIEW_BIZ_FEEDETAILINFO 
	                                    left outer join BIZ_PROJDETAIL on BIZ_PROJDETAIL.projdtlid = VIEW_BIZ_FEEDETAILINFO.projdtlid
                                    where VIEW_BIZ_FEEDETAILINFO.billid = {billid}";


                //获取数据
                DataSet dsInfo = SqlHelper.GetDataSet(sql, conn);

                List<FindWaitFNApproveBillDetailResultDataModel> lst = new List<FindWaitFNApproveBillDetailResultDataModel>();

                foreach (DataRow row in dsInfo.Tables[0].Rows)
                {
                    FindWaitFNApproveBillDetailResultDataModel data = new FindWaitFNApproveBillDetailResultDataModel();

                    data.billid = row["billid"].ToString().Trim();
                    data.objtp = row["objtp"].ToString().Trim();
                    data.amt_rectotaldtl = row["amt_rectotaldtl"].ToString().Trim();
                    data.amt_paytotal = row["amt_paytotal"].ToString().Trim();
                    data.dtlnum = row["dtlnum"].ToString().Trim();
                    data.num = row["num"].ToString().Trim();
                    data.amt_selprice = row["amt_selprice"].ToString().Trim();
                    data.amt_payprice = row["amt_payprice"].ToString().Trim();
                    data.xh = row["xh"].ToString().Trim();
                    data.amt_rectotal = row["amt_rectotal"].ToString().Trim();

                    lst.Add(data);
                }

                //取文件数
                sql = $@"select SYS_ARCHIVETB.*
                                from FIN_FEENOTICE
	                                inner join SYS_ARCHIVETB on SYS_ARCHIVETB.arckey = FIN_FEENOTICE.fnid
                                where FIN_FEENOTICE.billid = {billid}
	                                and SYS_ARCHIVETB.arcrule = 'BIZFN'";
                DataSet dsFiles = SqlHelper.GetDataSet(sql, conn);

                resultObj.files = dsFiles.Tables[0].Rows.Count.ToString();

                resultObj.code = "0";
                resultObj.message = "";
                resultObj.data = lst;
                return JsonConvert.SerializeObject(resultObj);
            }
            catch (Exception ex)
            {
                resultObj.code = "99";
                resultObj.message = ex.Message;
                resultObj.data = null;
                return JsonConvert.SerializeObject(resultObj);
            }
        }


        /// <summary>
        /// 获取结算单文件列表
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public string FindApproveFNFiles(FindApproveFileModel model)
        {
            string conn = Function.GetConnectionString();

            FindApproveFileResultModel resultObj = new FindApproveFileResultModel();

            try
            {
                if (model == null)
                {
                    throw new Exception("对象为空！");
                }
 

                if (string.IsNullOrEmpty(model.billid))
                {
                    throw new Exception("billid不能为空！");
                }
                 
                string billid = model.billid.Trim();


                string sql = $@"select SYS_ARCHIVETB.*
                                from FIN_FEENOTICE
	                                inner join SYS_ARCHIVETB on SYS_ARCHIVETB.arckey = FIN_FEENOTICE.fnid
                                where FIN_FEENOTICE.billid = {billid}
	                                and SYS_ARCHIVETB.arcrule = 'BIZFN'";

                //获取数据
                DataSet dsInfo = SqlHelper.GetDataSet(sql, conn);

                List<FindApproveFileResultDataModel> lst = new List<FindApproveFileResultDataModel>();


                string fileurl = System.Web.Configuration.WebConfigurationManager.AppSettings["FileUrl"];

                foreach (DataRow row in dsInfo.Tables[0].Rows)
                {
                    string extnm = row["extnm"].ToString().Trim().Replace(".", "").ToLower();

                    FindApproveFileResultDataModel data = new FindApproveFileResultDataModel();
                    data.realnm = row["filenm"].ToString().Trim();
                    data.filename = fileurl + @"/ywimage/" + row["arcpath"].ToString().Trim().Replace(@"\", @"/") + @"/" + row["filerealnm"].ToString().Trim();

                    if (extnm == "jpg" || extnm == "bmp" || extnm == "gif" || extnm == "png")
                    {
                        //图片
                        data.filetp = "img";
                    }
                    else
                    {
                        data.filetp = "doc";
                    }

                    lst.Add(data);
                }


                resultObj.code = "0";
                resultObj.message = "";
                resultObj.data = lst;
                return JsonConvert.SerializeObject(resultObj);
            }
            catch (Exception ex)
            {
                resultObj.code = "99";
                resultObj.message = ex.Message;
                resultObj.data = null;
                return JsonConvert.SerializeObject(resultObj);
            }
        }
    }
}
